﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using IFRS.Models;
using System.Configuration;

namespace IFRS.DataAccess.DAL
{
    public class SPR_TMP_INP_BOT_ONREPO
    {
        public DataTable SELECTBY_SESSION_ID(Paging paging, bool isAllBranch, string sCostCenter, string sessionId, ref int recordCount, ref int errorCount)
        {
            DataTable dt;
            var utility = new IFRS_Utility();
            var lstParams = new List<SqlParameter>();
            try
            {
                lstParams.Add(new SqlParameter("@ErrorCount", SqlDbType.Int));
                lstParams[0].Direction = ParameterDirection.Output;

                lstParams.Add(new SqlParameter("@RecordCount", SqlDbType.Int));
                lstParams[1].Direction = ParameterDirection.Output;

                lstParams.Add(new SqlParameter("@PageIndex", paging.PageIndex.AsDBValue()));
                lstParams.Add(new SqlParameter("@PageSize", paging.PageSize.AsDBValue()));
                lstParams.Add(new SqlParameter("@Sorting", paging.Sorting.AsDBValue()));

                lstParams.Add(new SqlParameter("@isAllBranch", isAllBranch.AsDBValue()));
                lstParams.Add(new SqlParameter("@sCostCenter", sCostCenter.AsDBValue()));

                lstParams.Add(new SqlParameter("@SESSION_ID", sessionId));
                dt = utility.StoredProcDataTable("SPR_TMP_INP_BOT_ONREPO_SELECTBY_SESSION_ID", lstParams.ToArray());

                if (!string.IsNullOrEmpty(Convert.ToString(lstParams[0].Value)))
                    errorCount = Convert.ToInt32(lstParams[0].Value);

                if (!string.IsNullOrEmpty(Convert.ToString(lstParams[1].Value)))
                    recordCount = Convert.ToInt32(lstParams[1].Value);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }
        public bool INSERT(List<TMP_INP_BOT_ONREPO> vTMP_INP_BOT_ONREPO)
        {
            bool result = true;
            string sConn = ConfigurationManager.ConnectionStrings["IFRS_WEB"].ConnectionString;
            SqlConnection Conn = null;
            SqlTransaction trn = null;
            int iNum = 0;
            try
            {
                Conn = new SqlConnection(sConn);
                Conn.Open();
                trn = Conn.BeginTransaction();
                SqlCommand Cmd = new SqlCommand("SPR_TMP_INP_BOT_ONREPO_INSERT", Conn);
                Cmd.CommandType = CommandType.StoredProcedure;
                foreach (TMP_INP_BOT_ONREPO TMP_BOT_ONREPO in vTMP_INP_BOT_ONREPO)
                {
                    Cmd.Parameters.Clear();
                    Cmd.Parameters.Add(new SqlParameter("@TMP_INP_BOT_ONREPO_ID", SqlDbType.Int));
                    Cmd.Parameters[0].Direction = ParameterDirection.Output;
                    Cmd.Parameters.Add(new SqlParameter("@SESSION_ID", TMP_BOT_ONREPO.SESSION_ID.AsDBValue()));
                    Cmd.Parameters.Add(new SqlParameter("@INPUT_DATE", TMP_BOT_ONREPO.INPUT_DATE.AsDBValue()));
                    Cmd.Parameters.Add(new SqlParameter("@TXN_ID", TMP_BOT_ONREPO.TXN_ID.AsDBValue()));
                    Cmd.Parameters.Add(new SqlParameter("@GL_ACCT_CD", TMP_BOT_ONREPO.GL_ACCT_CD.AsDBValue()));
                    Cmd.Parameters.Add(new SqlParameter("@COST_CENTER", TMP_BOT_ONREPO.COST_CENTER.AsDBValue()));
                    Cmd.Parameters.Add(new SqlParameter("@DAY_COUNT", TMP_BOT_ONREPO.DAY_COUNT.AsDBValue()));
                    Cmd.Parameters.Add(new SqlParameter("@CUR_GRS_RT", TMP_BOT_ONREPO.CUR_GRS_RT.AsDBValue()));
                    Cmd.Parameters.Add(new SqlParameter("@PAR_BAL", TMP_BOT_ONREPO.PAR_BAL.AsDBValue()));
                    Cmd.Parameters.Add(new SqlParameter("@CURRENCY", TMP_BOT_ONREPO.CURRENCY.AsDBValue()));
                    Cmd.Parameters.Add(new SqlParameter("@B_DAY_CONV", TMP_BOT_ONREPO.B_DAY_CONV.AsDBValue()));
                    Cmd.Parameters.Add(new SqlParameter("@B_DAY_CONV2", TMP_BOT_ONREPO.B_DAY_CONV2.AsDBValue()));
                    Cmd.Parameters.Add(new SqlParameter("@RP_GSB_PRDCD", TMP_BOT_ONREPO.RP_GSB_PRDCD.AsDBValue()));
                    Cmd.Parameters.Add(new SqlParameter("@RP_SOURCE_FILE", TMP_BOT_ONREPO.RP_SOURCE_FILE.AsDBValue()));
                    Cmd.Parameters.Add(new SqlParameter("@RP_MAPPING_CD", TMP_BOT_ONREPO.RP_MAPPING_CD.AsDBValue()));
                    Cmd.Parameters.Add(new SqlParameter("@HAS_ERROR", TMP_BOT_ONREPO.HAS_ERROR.AsDBValue()));
                    Cmd.Parameters.Add(new SqlParameter("@ERROR_DESC", TMP_BOT_ONREPO.ERROR_DESC.AsDBValue()));
                    Cmd.Parameters.Add(new SqlParameter("@CreatedDate", TMP_BOT_ONREPO.CreatedDate.AsDBValue()));
                    Cmd.Parameters.Add(new SqlParameter("@CreatedBy", TMP_BOT_ONREPO.CreatedBy.AsDBValue()));
                    Cmd.Parameters.Add(new SqlParameter("@CreatedCostCenter", TMP_BOT_ONREPO.CreatedCostCenter.AsDBValue()));
                    Cmd.Parameters.Add(new SqlParameter("@CreatedBranch", TMP_BOT_ONREPO.CreatedBranch.AsDBValue()));

                    Cmd.Transaction = trn;
                    iNum = Cmd.ExecuteNonQuery();
                    if (iNum == 0)
                    {
                        result = false;
                    }
                    trn.Commit();
                }
            }
            catch (Exception ex)
            {
                trn.Rollback();
                result = false;
                //throw ex;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
                result = true;
            }
            //int iNum = 0;
           
            //IFRS_Utility Utility = new IFRS_Utility();
            //List<SqlParameter> lstParams = new List<SqlParameter>();
            //try
            //{
            //    foreach (TMP_INP_BOT_ONREPO TMP_BOT_ONREPO in vTMP_INP_BOT_ONREPO)
            //    {
            //        lstParams.Add(new SqlParameter("@TMP_INP_BOT_ONREPO_ID", SqlDbType.Int));
            //        lstParams[0].Direction = ParameterDirection.Output;
            //        lstParams.Add(new SqlParameter("@SESSION_ID", TMP_BOT_ONREPO.SESSION_ID.AsDBValue()));
            //        lstParams.Add(new SqlParameter("@INPUT_DATE", TMP_BOT_ONREPO.INPUT_DATE.AsDBValue()));
            //        lstParams.Add(new SqlParameter("@TXN_ID", TMP_BOT_ONREPO.TXN_ID.AsDBValue()));
            //        lstParams.Add(new SqlParameter("@GL_ACCT_CD", TMP_BOT_ONREPO.GL_ACCT_CD.AsDBValue()));
            //        lstParams.Add(new SqlParameter("@COST_CENTER", TMP_BOT_ONREPO.COST_CENTER.AsDBValue()));
            //        lstParams.Add(new SqlParameter("@DAY_COUNT", TMP_BOT_ONREPO.DAY_COUNT.AsDBValue()));
            //        lstParams.Add(new SqlParameter("@CUR_GRS_RT", TMP_BOT_ONREPO.CUR_GRS_RT.AsDBValue()));
            //        lstParams.Add(new SqlParameter("@PAR_BAL", TMP_BOT_ONREPO.PAR_BAL.AsDBValue()));
            //        lstParams.Add(new SqlParameter("@CURRENCY", TMP_BOT_ONREPO.CURRENCY.AsDBValue()));
            //        lstParams.Add(new SqlParameter("@B_DAY_CONV", TMP_BOT_ONREPO.B_DAY_CONV.AsDBValue()));
            //        lstParams.Add(new SqlParameter("@B_DAY_CONV2", TMP_BOT_ONREPO.B_DAY_CONV2.AsDBValue()));
            //        lstParams.Add(new SqlParameter("@RP_GSB_PRDCD", TMP_BOT_ONREPO.RP_GSB_PRDCD.AsDBValue()));
            //        lstParams.Add(new SqlParameter("@RP_SOURCE_FILE", TMP_BOT_ONREPO.RP_SOURCE_FILE.AsDBValue()));
            //        lstParams.Add(new SqlParameter("@RP_MAPPING_CD", TMP_BOT_ONREPO.RP_MAPPING_CD.AsDBValue()));
            //        lstParams.Add(new SqlParameter("@HAS_ERROR", TMP_BOT_ONREPO.HAS_ERROR.AsDBValue()));
            //        lstParams.Add(new SqlParameter("@ERROR_DESC", TMP_BOT_ONREPO.ERROR_DESC.AsDBValue()));
            //        lstParams.Add(new SqlParameter("@CreatedDate", TMP_BOT_ONREPO.CreatedDate.AsDBValue()));
            //        lstParams.Add(new SqlParameter("@CreatedBy", TMP_BOT_ONREPO.CreatedBy.AsDBValue()));
            //        lstParams.Add(new SqlParameter("@CreatedCostCenter", TMP_BOT_ONREPO.CreatedCostCenter.AsDBValue()));
            //        lstParams.Add(new SqlParameter("@CreatedBranch", TMP_BOT_ONREPO.CreatedBranch.AsDBValue()));

            //        Utility.ExecuteStoredProc("SPR_TMP_INP_BOT_ONREPO_INSERT", lstParams.ToArray());

            //        iNum = (int)lstParams[0].Value;
            //        if (iNum == 0)
            //        {
            //            result = false;
            //        }
            //    }
            //}
            //catch (Exception ex)
            //{
            //    //throw ex;
            //    result = false;
            //}
            return result;
        }
    

        public DataTable EXPORT(int sessionId)
        {
            DataTable dt;
            var utility = new IFRS_Utility();
            var lstParams = new List<SqlParameter>();
            try
            {
                lstParams.Add(new SqlParameter("@SESSION_ID", sessionId));
                dt = utility.StoredProcDataTable("SPR_TMP_INP_BOT_ONREPO_EXPORT", lstParams.ToArray());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }
        public int IMPORT(int sessionId)
        {
            int iNum;
            var utility = new IFRS_Utility();
            var lstParams = new List<SqlParameter>();
            try
            {
                lstParams.Add(new SqlParameter("@SESSION_ID", sessionId));
                iNum = (int)utility.ExecuteStoredProc("SPR_TMP_INP_BOT_ONREPO_IMPORT", lstParams.ToArray());
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return iNum;
        }
    }
}
